package com.fare.taluo.queue;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region;

import com.fare.taluo.util.StringUtils;
import com.fare.taluo.util.convert.DoubleConvert;
import com.fare.taluo.vo.report.CsairReportVO;
import com.fare.util.ReportProperties;

public class ExcelCsairInternationlLinesReport {
	
	private static String title;//标题
			
	public static void setExcelCsairReport(Map<String ,List<Map<String ,CsairReportVO>>> csairVos) throws Exception{
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		title="国际航线官网渠道价差对比报表";
		HSSFSheet sheet = workbook.createSheet(title);
		
		// 设置单元格类型
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
		cellStyle.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
		cellStyle.setWrapText(true);
		
		
		HSSFCellStyle cellStyle_2= workbook.createCellStyle();
		cellStyle_2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyle_2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
		cellStyle_2.setFillForegroundColor(HSSFColor.ORANGE.index);
		cellStyle_2.setWrapText(true);
		
		
		HSSFCellStyle cellStyle_3= workbook.createCellStyle();
		cellStyle_3.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle_3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyle_3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
		cellStyle_3.setFillForegroundColor(HSSFColor.TEAL.index);
		cellStyle_3.setWrapText(true);
		
		HSSFCellStyle cellStyle_other= workbook.createCellStyle();
		cellStyle_other.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle_other.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
//		cellStyle_other.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
//		cellStyle_other.setFillForegroundColor(HSSFColor.TEAL.index);
		cellStyle_other.setWrapText(true);
		
		// 边框样式
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // 右边框
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // 上边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle.setLeftBorderColor(HSSFColor.PLUM.index);
		
		cellStyle_2.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // 右边框
		cellStyle_2.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // 上边框
		cellStyle_2.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle_2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle_2.setLeftBorderColor(HSSFColor.PLUM.index);
		
		cellStyle_3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // 右边框
		cellStyle_3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // 上边框
		cellStyle_3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle_3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		cellStyle_3.setLeftBorderColor(HSSFColor.PLUM.index);
		
		cellStyle_other.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
		cellStyle_other.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 上边框
		cellStyle_other.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle_other.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle_other.setLeftBorderColor(HSSFColor.PLUM.index);
		
		// 设置字体
		HSSFFont font = workbook.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 10);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗字体
		font.setColor(HSSFColor.WHITE.index);//白色字体
		
		HSSFFont font_other = workbook.createFont();
		font_other.setFontName("宋体");
		font_other.setFontHeightInPoints((short) 10);// 设置字体大小
//		font_other.setBoldweight(HSSFFont.);
//		font_other.setColor(HSSFColor.BLACK.index);//白色字体
		
		// ************************************************************
		// 产生表格行
		HSSFRow row = sheet.createRow((short) 0);// 第1行
		HSSFRow row_2 = sheet.createRow((short) 1);// 第2行
		HSSFRow row_3 = sheet.createRow((short) 2);//第3行
		row_3.setHeightInPoints((short) 22);// 设置ex单元格的高度
		row_2.setHeightInPoints((short) 22);
		
		
		//第1列
		HSSFCell cell = row_2.createCell((short) 0);
		// 单元格合并,四个参数分别是：起始行，起始列，结束行，结束列
		sheet.addMergedRegion(new Region(1, (short) 0, 2, (short) 0));
		cell.setCellStyle(cellStyle);
		cellStyle.setFont(font);
		cell.setCellValue("序号");
		setRegionStyle(sheet, new Region(1, (short) 0, 2, (short) 0), cellStyle);//加粗框
		//Region(int rowFrom, short colFrom, int rowTo, short colTo)
		
		//第2列
		HSSFCell cell_2 = row_2.createCell((short) 1);
		sheet.addMergedRegion(new Region(1, (short) 1, 2, (short) 1));
		cell_2.setCellStyle(cellStyle);
		cell_2.setCellValue("三字代码");
		setRegionStyle(sheet, new Region(1, (short) 1, 2, (short) 2), cellStyle);
		
		//第3列
		HSSFCell cell_3 = row_2.createCell((short) 2);
		sheet.addMergedRegion(new Region(1, (short) 2, 2, (short) 2));
		cell_3.setCellStyle(cellStyle);
		cell_3.setCellValue("航程类型");
		setRegionStyle(sheet, new Region(1, (short) 2, 2, (short) 3), cellStyle);
		
		//第4列
		HSSFCell cell_4 = row_2.createCell((short) 3);
		sheet.addMergedRegion(new Region(1, (short) 3, 2, (short) 3));
		cell_4.setCellStyle(cellStyle);
		cell_4.setCellValue("航班");
	//	setRegionStyle(sheet, new Region(1, (short) 3, 2, (short) 4), cellStyle);
		
		
		//第1行，第4列至第14列
		HSSFCell cell_5 = row_2.createCell((short) 4);
		sheet.addMergedRegion(new Region(1, (short) 4, 1, (short) 14));
		cell_5.setCellStyle(cellStyle);
		cell_5.setCellValue("(+7)("+com.fare.taluo.util.DateUtils.formatFutureDay(new Date(), "MM.dd", 7)+")");
		setRegionStyle(sheet, new Region(1, (short) 4, 1, (short) 14), cellStyle);
		
		//第1行，第4列至第14列
		HSSFCell cell_6 = row_2.createCell((short) 15);
		sheet.addMergedRegion(new Region(1, (short) 15, 1, (short) 25));
		cell_6.setCellStyle(cellStyle);
		cell_6.setCellValue("(+15)("+com.fare.taluo.util.DateUtils.formatFutureDay(new Date(), "MM.dd", 15)+")");
		setRegionStyle(sheet, new Region(1, (short) 15, 1, (short) 25), cellStyle);
		
		//第1行，第4列至第14列
		HSSFCell cell_7 = row_2.createCell((short) 26);
		sheet.addMergedRegion(new Region(1, (short) 26, 1, (short) 36));
		cell_7.setCellStyle(cellStyle);
		cell_7.setCellValue("(+30)("+com.fare.taluo.util.DateUtils.formatFutureDay(new Date(), "MM.dd", 30)+")");
		setRegionStyle(sheet, new Region(1, (short) 26, 1, (short) 36), cellStyle);
		
		//动态生成第3行，第4列至25列单元格
		int NO=4;
		for(int i=0;i<3;i++){
			//官网
			HSSFCell cell_5_2 = row_3.createCell((short) NO);
		//	sheet.addMergedRegion(new Region(2, (short) 4, 2, (short) 5));
			cell_5_2.setCellStyle(cellStyle);
			cell_5_2.setCellValue("官网");
			
			//淘宝
			HSSFCell cell_5_3 = row_3.createCell((short) NO+1);
			//sheet.addMergedRegion(new Region(2, (short) 5, 2, (short) 6));
			cell_5_3.setCellStyle(cellStyle_2);
			cell_5_3.setCellValue("淘宝");
			
			//代理名称
			HSSFCell cell_5_4 = row_3.createCell((short) NO+2);
			cell_5_4.setCellStyle(cellStyle_2);
			cell_5_4.setCellValue("代理名称");
			
			//代理人所在地
			HSSFCell cell_5_5 = row_3.createCell((short) NO+3);
			cell_5_5.setCellStyle(cellStyle_2);
			cell_5_5.setCellValue("代理人所在地");
			
			//价差金额
			HSSFCell cell_5_6 = row_3.createCell((short) NO+4);
			cell_5_6.setCellStyle(cellStyle_2);
			cell_5_6.setCellValue("价差金额");
			
			//价差
			HSSFCell cell_5_7 = row_3.createCell((short) NO+5);
			cell_5_7.setCellStyle(cellStyle_2);
			cell_5_7.setCellValue("价差");
			
			//去哪儿
			HSSFCell cell_5_8 = row_3.createCell((short) NO+6);
			cell_5_8.setCellStyle(cellStyle_3);
			cell_5_8.setCellValue("去哪儿");
			
			//代理名称
			HSSFCell cell_5_9 = row_3.createCell((short) NO+7);
			cell_5_9.setCellStyle(cellStyle_3);
			cell_5_9.setCellValue("代理名称");
			
			//代理人所在地
			HSSFCell cell_5_10 = row_3.createCell((short) NO+8);
			cell_5_10.setCellStyle(cellStyle_3);
			cell_5_10.setCellValue("代理人所在地");
			
			//价差金额
			HSSFCell cell_5_11 = row_3.createCell((short) NO+9);
			cell_5_11.setCellStyle(cellStyle_3);
			cell_5_11.setCellValue("价差金额");
			
			//价差
			HSSFCell cell_5_12 = row_3.createCell((short) NO+10);
			cell_5_12.setCellStyle(cellStyle_3);
			cell_5_12.setCellValue("价差");
			
			NO=NO+11;
			
		}
		//字体颜色
		cellStyle.setFont(font);
		sheet.createFreezePane(4,3);//冻结某列某行
		/**
		 * 设置列宽
		*/
		sheet.setColumnWidth(1, 12*256);
		sheet.setColumnWidth(2, 10*256);
		sheet.setColumnWidth(3, 16*256);
		sheet.setColumnWidth(6, 20*256);
		sheet.setColumnWidth(7, 30*256);
		sheet.setColumnWidth(12, 30*256);
		sheet.setColumnWidth(11, 20*256);
		sheet.setColumnWidth(18, 30*256);
		sheet.setColumnWidth(17, 20*256);
		sheet.setColumnWidth(23, 30*256);
		sheet.setColumnWidth(22, 20*256);
		sheet.setColumnWidth(29, 30*256);
		sheet.setColumnWidth(28, 20*256);
		sheet.setColumnWidth(34, 30*256);
		sheet.setColumnWidth(33, 20*256);
		
		//执行逻辑操作，填入数据
		//处理传入的数据
		
		int k=0;
		int strRow=3;//“三字码”合并开始行
		if(csairVos.size()>0){
			for(Map.Entry<String ,List<Map<String ,CsairReportVO>>> dataMaps:csairVos.entrySet()){
				String flightLinues=dataMaps.getKey();//航线
				
				strRow=strRow+k;
				int csairVoSize=0;
				csairVoSize=dataMaps.getValue().size();//此航线下共有多少条数据（用来合并单元格使用）
				//setRegionStyle(sheet, new Region(1, (short) 0, 2, (short) 0), cellStyle);//加粗框
				
				List<Map<String ,CsairReportVO>> csairVo=new ArrayList<Map<String,CsairReportVO>>();
				
				//在这里将数据排一下序，按航班日期、航线类型，航线
				Collections.sort(csairVo, new Comparator<Map<String, CsairReportVO>>(){
					@Override
					public int compare(Map<String, CsairReportVO> m1, Map<String, CsairReportVO> m2) {
						CsairReportVO o1 = m1.get("7") != null ? m1.get("7") : m1.get("15") != null ? m1.get("15") : m1.get("30");
						CsairReportVO o2 = m2.get("7") != null ? m2.get("7") : m2.get("15") != null ? m2.get("15") : m2.get("30");
						return o1.getFlightDate().compareTo(o2.getFlightDate()) != 0 ?
								o1.getFlightDate().compareTo(o2.getFlightDate())
								: o1.getFlightLine().compareTo(o2.getFlightLine()) != 0 ? o1.getFlightLine().compareTo(o2.getFlightLine())
										:o1.getFlightType()-o2.getFlightType();
					}
				});
				int flag=0;
				
				csairVo=dataMaps.getValue();
				if(csairVo.size()>0){//List<Map<String ,CsairReportVO>> 
					for(int i=0;i<csairVo.size();i++){
						k++;
						Map<String ,CsairReportVO> MapVo=new HashMap<String,CsairReportVO>();
						
						MapVo=csairVo.get(i);
						HSSFRow row_4 = sheet.createRow((short) k+2);//第4行开始
						
						if(flag==0){
//							HSSFRow row_flightL = sheet.createRow((short) k+3);//第4行开始
							HSSFCell cell_flightL = row_4.createCell((short) 1);
							// 单元格合并,四个参数分别是：起始行，起始列，结束行，结束列
							sheet.addMergedRegion(new Region(k+2, (short) 1, k+csairVoSize+1, (short) 1));
							cell_flightL.setCellStyle(cellStyle);
							cell_flightL.setCellValue(flightLinues);//航线
							setRegionStyle(sheet, new Region(k+2, (short) 1, k+csairVoSize+1, (short) 1), setHeadDateStyle(workbook));
						}
						flag++;
						
						
						if(MapVo.size()>0){//存在某一天数据
							
							int flag_7=0,flag_15=0,flag_30=0;
							for(Map.Entry<String, CsairReportVO> dataMap:csairVo.get(i).entrySet()){
								//setCellHead(HSSFRow row_4,CsairReportVO vo,HSSFWorkbook workbook,int k,String flgihtLines,int fromGerge,int toGerge,HSSFSheet sheet)
								if(dataMap.getKey().equals("7") && !dataMap.getValue().equals("") && dataMap.getValue() !=null){//未来7天的数据  4~14
									flag_7++;
									setCellHead(row_4, dataMap.getValue(), workbook, k);
									setDataCell(workbook,dataMap.getValue(),row_4,4,k);
								}
								if(dataMap.getKey().equals("15") && !dataMap.getValue().equals("") && dataMap.getValue() !=null){//未来15天的数据  15~25
									flag_15++;
									setCellHead(row_4, dataMap.getValue(), workbook, k);
									setDataCell(workbook,dataMap.getValue(),row_4,15,k);
								}
								if(dataMap.getKey().equals("30") && !dataMap.getValue().equals("") && dataMap.getValue() !=null){//未来30天的数据  26~36
									flag_30++;
									setCellHead(row_4, dataMap.getValue(), workbook, k);
									setDataCell(workbook,dataMap.getValue(),row_4,26,k);
								}
								
							}
							if(flag_7==0){
								//7天那些单元格数据都为空
								for(int m=4;m<15;m++){
									HSSFCell cell_null=row_4.createCell((short) m);
									cell_null.setCellValue("-");
									cell_null.setCellStyle(setNullDateStyle(workbook,k));
								}
							}if(flag_15==0){
								//15天那些单元格数据都为空
								for(int m=15;m<26;m++){
									HSSFCell cell_null=row_4.createCell((short) m);
									cell_null.setCellValue("-");
									cell_null.setCellStyle(setNullDateStyle(workbook,k));
								}
								
							}if(flag_30==0){
								//30天那些单元格数据都为空
								for(int m=26;m<37;m++){
									HSSFCell cell_null=row_4.createCell((short) m);
									cell_null.setCellValue("-");
									cell_null.setCellStyle(setNullDateStyle(workbook,k));
								}
							}
						}
						
						cellStyle_other.setFont(font_other);
						
					}
							
				}
				
			}
			
			
		}
		
		
		
		// 存盘test
		String strFilePath = ReportProperties.getInstance().getSavePath().endsWith("/")? ReportProperties.getInstance().getSavePath() :	ReportProperties.getInstance().getSavePath()+"/";
		Date nows=new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String Name=sdf.format(nows);
		String strFileName = "南航国际航空监控报表_"+Name+ ".xls";

			FileOutputStream fileOut = null;
			try {
				if (!(new File(strFilePath).isDirectory())) {
					new File(strFilePath).mkdirs();
				}
				strFilePath = strFilePath + strFileName;
				File fTarget = new File(strFilePath);
				fileOut = new FileOutputStream(fTarget);
				workbook.write(fileOut);
				System.out.println("SUCCESS");
			} catch (IOException e) {
				e.printStackTrace();
				e.getMessage();
			}finally{
				fileOut.close();
			}
	}
	
	/**
	 * 合并单元格加边框 水平
	 * 
	 * @param sheet
	 * @param region
	 * @param cs
	 */
	public static void setCellBorder(int start, int end, HSSFRow row,
			HSSFCellStyle style) {
		for (int i = start; i <= end; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellValue("");
			cell.setCellStyle(style);
		}
	}
	
	/**
	 * 合并单元格加边框,通用垂直水平
	 * 
	 * @param sheet
	 * @param region
	 * @param cs
	 */
	public static void setRegionStyle(HSSFSheet sheet, Region region,
			HSSFCellStyle cs) {
		int toprowNum = region.getRowFrom();
		for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {
			HSSFRow row = HSSFCellUtil.getRow(i, sheet);
			for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
				HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
				cell.setCellStyle(cs);
			}
		}
	}
	
	/**
	 * sheow cellHead
	 * k序号
	*/
	public static void setCellHead(HSSFRow row_4,CsairReportVO vo,HSSFWorkbook workbook,int k){
		HSSFCellStyle cellStyle_other= workbook.createCellStyle();
		cellStyle_other.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle_other.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
//		cellStyle_other.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
//		cellStyle_other.setFillForegroundColor(HSSFColor.TEAL.index);
		cellStyle_other.setWrapText(true);
		cellStyle_other.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
		cellStyle_other.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 上边框
		cellStyle_other.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle_other.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle_other.setLeftBorderColor(HSSFColor.PLUM.index);
		
		HSSFFont font_other = workbook.createFont();
		font_other.setFontName("宋体");
		font_other.setFontHeightInPoints((short) 10);// 设置字体大小
		
		
		
		HSSFCell cell_index = row_4.createCell((short) 0);
		cell_index.setCellValue(k);
		cell_index.setCellStyle(setHeadDateStyle(workbook));
		
		//三字代码
		/*HSSFCell cell_port=row_4.createCell((short) 1);
		cell_port.setCellValue(vo.getFromCity()+"-"+vo.getToCity());
		cell_port.setCellStyle(cellStyle_other);*/
		
		//航程类型
		HSSFCell cell_type=row_4.createCell((short) 2);
		cell_type.setCellValue(vo.getFlightType()==0?"单程":"来回程");
		cell_type.setCellStyle(setNullDateStyle(workbook,k));
		
		//航班
		HSSFCell cell_flightNo=row_4.createCell((short) 3);
		cell_flightNo.setCellValue(StringUtils.convert(vo.getFlightNo(), "-"));
		cell_flightNo.setCellStyle(setNullDateStyle(workbook,k));
		
	}
	/**
	 * show head style 
	*/
	
	public static HSSFCellStyle setHeadDateStyle(HSSFWorkbook workbook){
		HSSFCellStyle cellStyle_other= null;
		
		cellStyle_other= workbook.createCellStyle();
		cellStyle_other.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
		cellStyle_other.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyle_other.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
		cellStyle_other.setFillForegroundColor(new HSSFColor.LIGHT_YELLOW().getIndex());
		cellStyle_other.setWrapText(true);
		cellStyle_other.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
		cellStyle_other.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 上边框
		cellStyle_other.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle_other.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle_other.setLeftBorderColor(HSSFColor.PLUM.index);
		
		HSSFFont font_other = workbook.createFont();
		font_other.setFontName("宋体");
		font_other.setFontHeightInPoints((short) 10);// 设置字体大小
		cellStyle_other.setFont(font_other);
	
		
		return cellStyle_other;
	}
	/**
	 * show null data 
	*/
	public static HSSFCellStyle setNullDateStyle(HSSFWorkbook workbook,int k){
		HSSFCellStyle cellStyle_other= null;
		if(k %2==0){
			cellStyle_other= workbook.createCellStyle();
			cellStyle_other.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
//			cellStyle_other.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
//			cellStyle_other.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
			cellStyle_other.setFillForegroundColor(HSSFColor.TEAL.index);
			cellStyle_other.setWrapText(true);
			cellStyle_other.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
			cellStyle_other.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 上边框
			cellStyle_other.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle_other.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle_other.setLeftBorderColor(HSSFColor.PLUM.index);
		}else{
			cellStyle_other= workbook.createCellStyle();
			cellStyle_other.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
			cellStyle_other.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
			cellStyle_other.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
			cellStyle_other.setFillForegroundColor(new HSSFColor.LIGHT_TURQUOISE().getIndex());
			cellStyle_other.setWrapText(true);
			cellStyle_other.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
			cellStyle_other.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 上边框
			cellStyle_other.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle_other.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle_other.setLeftBorderColor(HSSFColor.PLUM.index);
			
		}
		
		HSSFFont font_other = workbook.createFont();
		font_other.setFontName("宋体");
		font_other.setFontHeightInPoints((short) 10);// 设置字体大小
		cellStyle_other.setFont(font_other);
		
		return cellStyle_other;
	}
	
	/**
	 * sheow cell
	*/
	public static void setDataCell( HSSFWorkbook workbook,CsairReportVO vos,HSSFRow row_4,int k ,int t){
		HSSFCellStyle cellStyle_other=null;
		if(t%2==0){
			cellStyle_other= workbook.createCellStyle();
			cellStyle_other.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
//			cellStyle_other.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
//			cellStyle_other.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
			cellStyle_other.setFillForegroundColor(HSSFColor.TEAL.index);
			cellStyle_other.setWrapText(true);
			cellStyle_other.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
			cellStyle_other.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 上边框
			cellStyle_other.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle_other.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle_other.setLeftBorderColor(HSSFColor.PLUM.index);
			
		}else{
			cellStyle_other= workbook.createCellStyle();
			cellStyle_other.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
			cellStyle_other.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
			cellStyle_other.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );//前景色(此前景色为纯色：SOLID_FOREGROUND)，在前景色的基本下才能设定背景颜色
			cellStyle_other.setFillForegroundColor(new HSSFColor.LIGHT_TURQUOISE().getIndex());
			cellStyle_other.setWrapText(true);
			cellStyle_other.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
			cellStyle_other.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 上边框
			cellStyle_other.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle_other.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle_other.setLeftBorderColor(HSSFColor.PLUM.index);
		
		
		}
		
		HSSFFont font_other = workbook.createFont();
		font_other.setFontName("宋体");
		font_other.setFontHeightInPoints((short) 10);// 设置字体大小
		
		//官网
		HSSFCell cell_price=row_4.createCell((short) k);
		cell_price.setCellValue(DoubleConvert.convert(vos.getPrice(), "-", 0));
		cell_price.setCellStyle(cellStyle_other);
		
		//淘宝
		HSSFCell cell_taobaoprice=row_4.createCell((short) k+1);
		cell_taobaoprice.setCellValue(DoubleConvert.convert(vos.getTaobaoPric(), "-", 0));
		cell_taobaoprice.setCellStyle(cellStyle_other);
		
		//代理商名称
		HSSFCell cell_agentname=row_4.createCell((short) k+2);
		cell_agentname.setCellValue(StringUtils.convert(vos.getTaobaoAgentName(), "-"));
		cell_agentname.setCellStyle(cellStyle_other);
		
		//代理人所在地
		HSSFCell cell_agentddress=row_4.createCell((short) k+3);
		cell_agentddress.setCellValue(StringUtils.convert(vos.getTabaoAgentAddess(), "-"));
		cell_agentddress.setCellStyle(cellStyle_other);
		
		//价差金额
		HSSFCell cell_spreadAmount=row_4.createCell((short) k+4);
		cell_spreadAmount.setCellValue(DoubleConvert.convert(vos.getTaobaoSpreadAmount(), "-", 0));
		cell_spreadAmount.setCellStyle(cellStyle_other);
		
		//价差
		HSSFCell cell_spread=row_4.createCell((short) k+5);
		cell_spread.setCellValue(DoubleConvert.convertPercent(vos.getTaobaoSpread(), "-", 2));
		cell_spread.setCellStyle(cellStyle_other);
		
		//去哪儿
		HSSFCell cell_qprice=row_4.createCell((short) k+6);
		cell_qprice.setCellValue(DoubleConvert.convert(vos.getQunaerPrice(), "-", 0));
		cell_qprice.setCellStyle(cellStyle_other);
		
		//代理名称
		HSSFCell cell_qagentName=row_4.createCell((short) k+7);
		cell_qagentName.setCellValue(StringUtils.convert(vos.getQunaerAgentName(), "-"));
		cell_qagentName.setCellStyle(cellStyle_other);
		
		//代理人所在地
		HSSFCell cell_qagentNameAddress=row_4.createCell((short) k+8);
		cell_qagentNameAddress.setCellValue(StringUtils.convert(vos.getQunaerAgentAdderss(), "-"));
		cell_qagentNameAddress.setCellStyle(cellStyle_other);
		
		//价差金额
		HSSFCell cell_qspreadAm=row_4.createCell((short) k+9);
		cell_qspreadAm.setCellValue(DoubleConvert.convert(vos.getQunaerSpreadAmount(), "-", 0));
		cell_qspreadAm.setCellStyle(cellStyle_other);
		
		//价差
		HSSFCell cell_qspread=row_4.createCell((short) k+10);
		cell_qspread.setCellValue(DoubleConvert.convertPercent(vos.getQunaerSpread(), "-", 2));
		cell_qspread.setCellStyle(cellStyle_other);
		cellStyle_other.setFont(font_other);
	}
	
	public static void main(String[] args) {//test
		try {
			Map<String ,List<Map<String ,CsairReportVO>>> csairVo =new HashMap<String, List<Map<String,CsairReportVO>>>();
			Map<String ,CsairReportVO> dataMap=new HashMap<String, CsairReportVO>();
			Map<String ,CsairReportVO> dataMap2=new HashMap<String, CsairReportVO>();
			Map<String ,CsairReportVO> dataMap3=new HashMap<String, CsairReportVO>();
			CsairReportVO vo=new CsairReportVO();
			vo.setGrabTime(new Date());
			vo.setDeprotDate(new Date());
			vo.setFlightDate(new Date());
			vo.setFlightNo("CZ1111");
			vo.setFlightType(1);
			vo.setFromCity("PEK");
			vo.setFromCityName("北京");
			vo.setId(1111111111L);
			vo.setPrice(44.0);
			vo.setQunaerAgentAdderss("广州");
			vo.setQunaerAgentName("代理商名字");
			vo.setQunaerPrice(32.0);
			vo.setQunaerSpread(0.1);
			vo.setQunaerSpreadAmount(10.0);
			vo.setTabaoAgentAddess("taobao代理商地址");
			vo.setTaobaoAgentName("taobao代理商名字");
			vo.setTaobaoPric(222.2);
			vo.setTaobaoSpread(22.2);
			vo.setTaobaoSpreadAmount(11.1);
			vo.setToCity("CAN");
			vo.setToCityName("广州");
			
			List<Map<String ,CsairReportVO>> voLists=new ArrayList<Map<String,CsairReportVO>>();
			List<Map<String ,CsairReportVO>> voLists2=new ArrayList<Map<String,CsairReportVO>>();
			List<Map<String ,CsairReportVO>> voLists3=new ArrayList<Map<String,CsairReportVO>>();
			
			dataMap.put("15", vo);
			dataMap.put("7", vo);
			dataMap.put("30", vo);
			dataMap2.put("15", vo);
			dataMap3.put("7", vo);
			dataMap3.put("30", vo);
			
			voLists.add(dataMap);
			voLists.add(dataMap3);
			voLists.add(dataMap2);
			voLists.add(dataMap3);
			
			voLists2.add(dataMap);
			voLists2.add(dataMap3);
			voLists2.add(dataMap2);
			
			voLists3.add(dataMap);
			voLists3.add(dataMap3);
			voLists3.add(dataMap2);
			
			csairVo.put("CAN-PEK", voLists);
			csairVo.put("CAN-JJS", voLists2);
			csairVo.put("SHA-JND", voLists3);
			//Map<String ,List<Map<String ,CsairReportVO>>>
			ExcelCsairInternationlLinesReport.setExcelCsairReport(csairVo);//test
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
